{
 "cells": [
  {
   "cell_type": "code",
   "execution_count": null,
   "metadata": {},
   "outputs": [],
   "source": [
    "#coding=utf-8\n",
    "import pandas as pd\n",
    "import matplotlib  \n",
    "import matplotlib.pyplot as plt \n",
    "import MySQLdb\n",
    "import sys\n",
    "from matplotlib.font_manager import FontProperties  \n",
    "\n",
    "myfont = FontProperties(fname='/usr/share/fonts/chinese/TrueType/SIMHEI.TTF')  \n",
    "matplotlib.rcParams['axes.unicode_minus']=False "
   ]
  },
  {
   "cell_type": "code",
   "execution_count": null,
   "metadata": {},
   "outputs": [],
   "source": [
    "con_db = MySQLdb.connect(host='127.0.0.1',port=3306, user='xbu', db='test',use_unicode=True, charset='utf8')\n",
    "# data_filter_mysql = 'select %s from %s where month(last_update)=%s limit 100'%('last_update','acct_info201609',10) \n",
    "data_filter_mysql = 'select * from %s '% 'jdinfo' "
   ]
  },
  {
   "cell_type": "code",
   "execution_count": null,
   "metadata": {
    "scrolled": true
   },
   "outputs": [],
   "source": [
    "reload(sys)\n",
    "sys.setdefaultencoding(\"utf-8\")\n",
    "\n",
    "col_name = ['productName', 'productColor', 'orderTime', 'score', 'userClientShow', \n",
    "            'userLevelName','commentsTime', 'cmtplusDays','product_id']\n",
    "jd = pd.read_sql(data_filter_mysql,con=con_db)\n",
    "# jd = pd.read_table('/home/log/xbu/笔记本_2018-02-02-10-24.txt',sep = '|', names=col_name)\n",
    "jd['userClientShow'] = jd['userClientShow'].apply(lambda x: \"来自PC客户端\" if str(x)==u'' else x)\n",
    "jd.info()"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": null,
   "metadata": {},
   "outputs": [],
   "source": [
    "jd[4:6]"
   ]
  },
  {
   "cell_type": "markdown",
   "metadata": {},
   "source": [
    "会员购买分布"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": null,
   "metadata": {},
   "outputs": [],
   "source": [
    "buyer_info = pd.value_counts(jd.userLevelName)\n",
    "client_sw = pd.value_counts(jd.userClientShow)"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": null,
   "metadata": {},
   "outputs": [],
   "source": [
    "labels_buyer = [label.decode('utf-8') for label in buyer_info.index.values ]\n",
    "ax1 = buyer_info.plot(kind='bar',)\n",
    "ax1.set_xticklabels(labels_buyer, fontproperties=myfont) \n",
    "plt.show()\n",
    "buyer_info"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": null,
   "metadata": {},
   "outputs": [],
   "source": [
    "labels_sw = [label.decode('utf-8') for label in client_sw.index.values ]\n",
    "ax2 = client_sw.plot(kind='bar',)\n",
    "ax2.set_xticklabels(labels_sw, fontproperties=myfont) \n",
    "plt.show(ax2)\n",
    "client_sw"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": null,
   "metadata": {},
   "outputs": [],
   "source": [
    "from collections import defaultdict\n",
    "total = client_sw.sum().astype(float)\n",
    "\n",
    "product_percent = defaultdict()\n",
    "\n",
    "for i in client_sw.index:\n",
    "    percent = client_sw[i].sum()/ total \n",
    "    product_percent[i] = percent*100\n",
    "    \n",
    "product_percent"
   ]
  },
  {
   "cell_type": "markdown",
   "metadata": {},
   "source": [
    "##购买时间序列"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": null,
   "metadata": {},
   "outputs": [],
   "source": [
    "new2 = jd['orderTime'].groupby(jd['orderTime'].map(lambda x:x[11:13]))\n",
    "new2.count().plot()"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": null,
   "metadata": {},
   "outputs": [],
   "source": [
    "####way1######\n",
    "# jd.set_index(\"order_Time\", inplace=True)\n",
    "# jd.index = pd.DatetimeIndex(jd.index)\n",
    "\n",
    "#####way2#######\n",
    "jd['commentTime'] = pd.to_datetime(jd['commentTime'])\n",
    "jd['orderTime'] = pd.to_datetime(jd['orderTime'])\n",
    "jd.set_index(\"orderTime\", inplace=True)"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": null,
   "metadata": {},
   "outputs": [],
   "source": [
    "jd.index"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": null,
   "metadata": {
    "scrolled": true
   },
   "outputs": [],
   "source": [
    "new1 = jd['productName'].loc['2017-11-01':'2018-01-31'].resample('D').count()\n",
    "new1.plot()\n",
    "jd2 = jd.drop(jd.loc['2017-11-11'].index)\n",
    "jd3 = jd2.drop(jd.loc['2017-12-12'].index)\n",
    "new2 = jd3['productName'].loc['2017-11-01':'2018-01-31'].resample('D').count()\n",
    "new2.plot()"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": null,
   "metadata": {},
   "outputs": [],
   "source": [
    "jd[:5]\n",
    "delta_time = jd.commentTime - jd.index\n",
    "delta_time.describe()"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": null,
   "metadata": {
    "scrolled": true
   },
   "outputs": [],
   "source": [
    "product_cat = pd.value_counts(jd.productId)\n",
    "product_cat"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": null,
   "metadata": {},
   "outputs": [],
   "source": []
  }
 ],
 "metadata": {
  "kernelspec": {
   "display_name": "Python 2",
   "language": "python",
   "name": "python2"
  },
  "language_info": {
   "codemirror_mode": {
    "name": "ipython",
    "version": 2
   },
   "file_extension": ".py",
   "mimetype": "text/x-python",
   "name": "python",
   "nbconvert_exporter": "python",
   "pygments_lexer": "ipython2",
   "version": "2.7.5"
  }
 },
 "nbformat": 4,
 "nbformat_minor": 2
}
